﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;

namespace ProvaBD
{
    class AlumneConversor:Alumne
    {
        BD bd;
        public const int agregar = 1;
        public const int modificar = 2;
        public const int eliminar = 3;
        public AlumneConversor()
        {
            bd=new BD();
            bd.Connectarse();
            bd.propertyConexxio.Open();
        }

        public void agregarAlumne()
        {
            agregarOModificarAlumne(agregar);
        }

        public void modificarAlumne()
        {
            agregarOModificarAlumne(modificar);
        }

        public void eliminarAlumne()
        {
            agregarOModificarAlumne(eliminar);
        }
        public void agregarOModificarAlumne(int fer)
        {

            if (bd.propertyConexxio.State != System.Data.ConnectionState.Open)
                bd.propertyConexxio.Open();
            Console.WriteLine(sql(fer));
            SqlCeCommand com = new SqlCeCommand(sql(fer), bd.propertyConexxio);
            com.ExecuteNonQuery();
            bd.propertyConexxio.Close();
        }
        private String sql(int fer)
        {
            if (fer==agregar)
            {
                return "INSERT INTO alumnes (nom,Cognom,Telefon) VALUES ('" + propertyNom + "','"
                    + propertyCognom + "'," + propertyTelefon + ")";
            }
            else if (fer == modificar)
            {
                //Fem el modificar
                return "UPDATE alumnes SET nom='" + propertyNom + "', cognom ='" + propertyCognom + "'" +
                " ,Telefon = " + propertyTelefon + " WHERE nMatricula=" + this.propertyMatricula;
            }
            else
            {
                return "DELETE FROM alumnes WHERE nMatricula="+ this.propertyMatricula;
            }
        }
        public String obtenirTelefon(int id)
        {
            if (bd.propertyConexxio.State != System.Data.ConnectionState.Open)
                bd.propertyConexxio.Open();
            SqlCeCommand com = new SqlCeCommand("SELECT Telefon FROM alumnes WHERE nMatricula=" + id, bd.propertyConexxio);
            SqlCeDataReader r = com.ExecuteReader();
            r.Read();
            return r["Telefon"].ToString();
        }
    }
}
